package com.pwt.repository;

import com.pwt.common.ResultPage;
import com.pwt.entity.PowerModule;
import com.pwt.entity.WERTEVAR;
import com.pwt.model.*;
import com.pwt.model.crustallus.*;
import com.pwt.model.crustallus.FeatureModel;
import com.pwt.model.what.CharacteristicsModel;
import com.pwt.model.what.HeaderModel;
import com.pwt.model.what.OverviewModel;
import com.pwt.repository.base.DataRepositoryExtension;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;

/**
 * @author dw
 * @version 1.0.0
 * @date 2021/9/23
 */
@Transactional(rollbackFor = Exception.class)
public class WERTEVARRepositoryExtensionImpl extends DataRepositoryExtension<WERTEVAR, Integer>implements WERTEVARRepositoryExtension{

    @Override
    public List<PowerModule> queryMASCHINE(String beginTime, String endTime) {
        HashMap<String,Object> map = new HashMap<>();
        String sql = "SELECT a.k0053,a.k0010,a.k1000,a.k2005,a.[count],\n" +
                "( SELECT t.TETEILNR FROM TEIL t WHERE a.k1000= t.TETEIL ) AS k1001,\n" +
                "( SELECT t.TEBEZEICH FROM TEIL t WHERE a.k1000= t.TETEIL ) AS k1002\n" +
                "FROM(\n" +
                "SELECT\n" +
                "w.WVAUFTRAG AS k0053,\n" +
                "w.WVMASCHINE k0010,\n" +
                "w.WVTEIL AS k1000,\n" +
                "m.MEMERKKLASSE k2005,\n" +
                "COUNT(WVWERT) [count]\n" +
                "FROM\n" +
                "WERTEVAR w\n" +
                "LEFT JOIN (SELECT METEIL,MEMERkMAL,MEMERKKLASSE from MERkMAL) m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                "where w.WVDATZEIT >= :beginTime and w.WVDATZEIT <= :endTime and w.WVMASCHINE is not null \n" +
                "GROUP BY w.WVAUFTRAG,w.WVMASCHINE,w.WVTEIL,m.MEMERKKLASSE\n" +
                ") a";
                map.put("beginTime",beginTime);
                map.put("endTime",endTime);
        return super.queryCustomBeanListBySql(PowerModule.class,sql,map);
    }


    @Override
    public HeaderModel getHeader(String k0014, Integer k0010) {

        String sql = "SELECT top 1\n" +
                "  w.WV0014 k0014,\n" +
                "\tw.WVDATZEIT k0004,\n" +
                "\tw.WVCHARGE k0006," +
                "\tt.TEBEZEICH k1002,\n" +
                "\tt.TETEIL k1000,\n" +
                "\tp.pNumber k4092,\n" +
                "\tp.pName k4093,\n" +
                "\tp.mNumber k0010,\n" +
                "\tp.mName machine\n" +
                "from WERTEVAR  w\n" +
                "LEFT JOIN  MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                "LEFT JOIN  TEIL t on w.WVTEIL=t.TETEIL\n" +
                "LEFT JOIN  (SELECT m.MAMASCHINE mId,m.MANR as mNumber,m.MABEZ mName,p.PRPRUEFER pId,p.PRNAME pNumber,p.PRVORNAME pName\n" +
                "from MASCHINE m\n" +
                "left JOIN ZU_MA_MAk z on m.MAMASCHINE =z.MAkEY\n" +
                "LEFT JOIN MASCHINkAT ma on ma.MAkNR=z.MAkNR\n" +
                "LEFT JOIN PRUEFER p on p.PRNAME = ma.MAkBEZ) p on p.mId=w.WVMASCHINE\n" +
                "where w.WVATTRIBUT!= 255 and  w.WV0014=?0  and w.WVMASCHINE=?1 order by w.WVDATZEIT desc";
        return  super.queryCustomBeanBySql(HeaderModel.class,sql,k0014,k0010);
    }

    @Override
    public String hasError(String k0014, Integer k0010) {
        String sql ="SELECT\n" +
                " top 1 " +
                " w.WV0005 \n" +
                "from WERTEVAR  w\n" +
                "LEFT JOIN  MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                "LEFT JOIN  TEIL t on w.WVTEIL=t.TETEIL\n" +
                "where w.WVATTRIBUT!= 255 and  w.WV0014=?0  and w.WVMASCHINE=?1  and ((w.WVWERT > m.MEUGW or w.WVWERT < m.MEOGW))";
        return super.queryStringBySql(sql,k0014,k0010);
    }

    @Override
    public ResultPage<CharacteristicsModel> getList(String k0014, Integer k0010, Integer page, Integer limit) {

        String sql = " select \n" +
                "\tm.METEIL k1000,\n" +
                "\tm.MEMERkMAL k2000,\n" +
                "\tm.MEMERkBEZ k2002,\n" +
                "\t(case when m.MENENNMAS is null and m.MEUGW is not null and m.MEOGW is not null then (m.MEUGW+m.MEOGW)/2 end) k2101,\n" +
                "\tw.WVDATZEIT  dateTime,\n" +
                "\tm.MEUGW k2110,\n" +
                "\tm.MEOGW k2111,\n" +
                "\tm.MEUGSCHROTT K2114,\n" +
                "\tm.MEOGSCHROTT K2115,\n" +
                "\tw.WVWERT k0001,\n" +
                "\tm.MEEINHEITTEXT k2142,\n" +
                "\tm.MEUNTERSART k2009" +
                " from  WERTEVAR  w\n" +
                " LEFT JOIN  MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                " LEFT JOIN  TEIL t on w.WVTEIL=t.TETEIL\n" +
                " where w.WVATTRIBUT!= 255 and  w.WV0014=?0  and w.WVMASCHINE=?1 ";
        String cSql = "SELECT\n " +
                " count(1) " +
                " from WERTEVAR  w\n" +
                " LEFT JOIN  MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                " LEFT JOIN  TEIL t on w.WVTEIL=t.TETEIL\n" +
                " where w.WVATTRIBUT!= 255 and  w.WV0014=?0  and w.WVMASCHINE=?1 ";
        return super.queryCustomBeanPageBySql(CharacteristicsModel.class,cSql,sql,page-1,limit,k0014,k0010);
    }

    public List<Double> getMeasuredValue(Integer k1000,Short k2000,Integer k0010){
        String sql = "SELECT WVWERT from WERTEVAR   where WVATTRIBUT!= 255 and   WVTEIL=?0 and WVMERkMAL=?1 and WVMASCHINE=?2 order by WVDATZEIT desc";
        return super.queryDoubleListBySql(sql,k1000, k2000, k0010);
    };

    public List<Double> getMeasureValueOnMonth(Integer k1000,Short k2000,Integer month){
        String sql ="SELECT WVWERT from WERTEVAR where WVATTRIBUT!= 255 and   WVTEIL=?1 and WVMERkMAL=?2 and DateDiff(mm,WVDATZEIT,getdate())  BETWEEN 0 AND ?3 order by WVDATZEIT desc";
        return super.queryDoubleListBySql(sql,k1000,k2000,month);
    };


    @Override
    public List<Double> getMeasureValueOnTop(Integer num, Integer k1000, Short k2000) {
        String sql = "SELECT  top "+num+"  WVWERT from WERTEVAR  where WVATTRIBUT!= 255 and   WVTEIL=?0 and WVMERkMAL=?1 order by WVDATZEIT desc";
        return super.queryDoubleListBySql(sql,  k1000,  k2000);
    }

    @Override
    public ResultPage<CharacteristicsModel> getMaxAndMinCharacteristics(String k0014, Integer k0010, Integer page, Integer limit) {
        String sql = "SELECT \n" +
                "\tm.METEIL k1000,\n" +
                "\tm.MEMERkMAL k2000,\n" +
                "\tm.MEMERkBEZ k2002,\n" +
                "\t(case when m.MENENNMAS is null and m.MEUGW is not null and m.MEOGW is not null then (m.MEUGW+m.MEOGW)/2 end) k2101,\n" +
                " w.WVDATZEIT as dateTime,\n" +
                "\tm.MEUGW k2110,\n" +
                "\tm.MEOGW k2111,\n" +
                "\tm.MEUGSCHROTT K2114,\n" +
                "\tm.MEOGSCHROTT K2115,\n" +
                "\tw.WVWERT k0001,\n" +
                "\tm.MEEINHEITTEXT k2142,\n" +
                "\tm.MEUNTERSART k2009," +
                "(case when w.WVWERT>m.MEOGW or w.WVWERT<m.MEUGW then 0 else 1 end) state \n" +
                " from WERTEVAR  w\n" +
                " LEFT JOIN  MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                " LEFT JOIN  TEIL t on w.WVTEIL=t.TETEIL\n" +
                " where w.WVATTRIBUT!= 255 and  w.WV0014=?0  and w.WVMASCHINE=?1  ";
        String cSql = "SELECT\n " +
                " count(1) " +
                " from WERTEVAR  w\n" +
                " LEFT JOIN  MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                " LEFT JOIN  TEIL t on w.WVTEIL=t.TETEIL\n" +
                " where w.WVATTRIBUT!= 255 and  w.WV0014=?0  and w.WVMASCHINE=?1  ";
        return super.queryCustomBeanPageBySql(CharacteristicsModel.class,cSql,sql,page-1,limit,k0014,k0010);
    }

    @Override
    public List<ChartValueModel> getListsAllValues(Integer k1000, Short k2000, Integer k0010, String k0014, Integer top) {
        String sql= "SELECT  \n";
        if(top != null){
            sql+= " top "+top;
        }
        sql+="\tm.METEIL k1000,\n" +
                "\tt.TEBEZEICH k1002,\n" +
                "\tm.MEMERkMAL k2000,\n" +
                "\tm.MEMERkBEZ k2002,\n" +
                "  w.WV0014 k0014,\n" +
                " ISNULL(w.WV0005, 'ok') k0005, \n" +
                "(case when w.WV0005 is null or w.WV0005='OK' then 1 else 0 end) scrap, \n" +
                " w.WVDATZEIT as dateTime,\n" +
                "\tw.WVCHARGE k0006,\n" +
                "\tm.MEUNTERSART  K2009 ," +
                "\tm.MEEINHEITTEXT K2142,\n" +
                "\tw.WVWERT k0001,\n" +
                " (CASE\n" +
                " WHEN w.WVWERT >= m.MEUGW and w.WVWERT <= m.MEOGW THEN 1 \n" +
                " WHEN w.WVWERT < (m.MEUGSCHROTT) or w.WVWERT > (m.MEOGSCHROTT) THEN 2 \n" +
                "ELSE 3 END) state\n" +
                "from WERTEVAR  w\n" +
                "LEFT JOIN  MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                "LEFT JOIN  TEIL t on w.WVTEIL=t.TETEIL\n" +
                "where w.WVATTRIBUT!= 255 and  w.WVTEIL=?0 and w.WVMERkMAL=?1  and w.WVMASCHINE=?2  and w.WVDATZEIT<=(select top 1 WVDATZEIT from WERTEVAR where WV0014=?3 order by  WVDATZEIT desc) order by w.WVDATZEIT ";
        return super.queryCustomBeanListBySql(ChartValueModel.class,sql,k1000,k2000,k0010,k0014);
    }

    public List<Double> getWertOrderByK0014(Integer k1000, Short k2000, Integer k0010, String k0014) {
        String sql = "select top 125 w.WVWERT "+
                "from WERTEVAR  w\n" +
                "where w.WVATTRIBUT!= 255 and  w.WVTEIL=?0 and w.WVMERkMAL=?1  and w.WVMASCHINE=?2  and w.WVDATZEIT<=(select top 1 WVDATZEIT from WERTEVAR where WV0014=?3 order by  WVDATZEIT desc) ";
        return super.queryDoubleListBySql(sql,k1000,k2000,k0010,k0014);
    }

    public ResultPage<OverviewModel> getOverviewModel(String k0014, Integer k0010, Integer page, Integer limit){
        String sql = "SELECT \n" +
                "w.WVCHARGE k0006,\n" +
                "m.METEIL k1000,\n" +
                "m.MEMERkMAL k2000,\n" +
                "m.MEMERkBEZ k2002,\n" +
                "m.MEUGW k2110,\n" +
                "m.MEOGW k2111,\n" +
                "(case when m.MENENNMAS is null and m.MEUGW is not null and m.MEOGW is not null then (m.MEUGW+m.MEOGW)/2 end) k2101,\n" +
                "cp.cp as cpTarget,\n" +
                "cp.cpk as cpkTarget\n" +
                "from WERTEVAR  w\n" +
                "LEFT JOIN MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                "left join cp_config cp on cp.importance_rank=m.MEMERKKLASSE\n" +
                "where w.WVATTRIBUT!= 255 and  w.WV0014=?0  and w.WVMASCHINE=?1";
        String cSql = "select count(1) from " +
                "WERTEVAR  w\n" +
                "LEFT JOIN MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                "where w.WVATTRIBUT!= 255 and  w.WV0014=?0  and w.WVMASCHINE=?1";
        return super.queryCustomBeanPageBySql(OverviewModel.class,cSql,sql,page-1,limit,k0014,k0010);
    }


    public ResultPage<OverviewModel> getCharacteristicsSigmaByOrderId(String k0053 ,Integer k1000, Integer k0010,String startTime, String endTime,Integer page, Integer limit){
        String sql = "SELECT \n" +
                "ma.MANR k4092,\n" +
                "ma.MABEZ k4093,\n" +
                "m.METEIL k1000,\n" +
                "m.MEMERkMAL k2000,\n" +
                "m.MEMERkBEZ k2002,\n" +
                "m.MEUGW k2110,\n" +
                "m.MEOGW k2111,\n" +
                "(case when m.MENENNMAS is null and m.MEUGW is not null and m.MEOGW is not null then (m.MEUGW+m.MEOGW)/2 end) k2101," +
                "cp.cp as cpTarget,\n" +
                "cp.cpk as cpkTarget\n" +
                "from (select WVAUFTRAG,WVMASCHINE,WVTEIL,WVMERkMAL from WERTEVAR where WVATTRIBUT!= 255 and  WVDATZEIT >='"+startTime+ "' and WVDATZEIT <='"+endTime+ "' GROUP BY WVAUFTRAG,WVMASCHINE,WVTEIL,WVMERkMAL)  w\n" +
                "LEFT JOIN MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                "LEFT JOIN  MASCHINE ma on ma.MAMASCHINE=w.WVMASCHINE\n" +
                "left join cp_config cp on cp.importance_rank=m.MEMERKKLASSE\n" +
                "where   w.WVMASCHINE=?0 and w.WVTEIL=?1  ";
        String cSql = "select count(1) from " +
                "(select WVAUFTRAG,WVMASCHINE,WVTEIL,WVMERkMAL from WERTEVAR where WVATTRIBUT!= 255 and  WVDATZEIT >='"+startTime+ "' and WVDATZEIT <='"+endTime+ "' GROUP BY WVAUFTRAG,WVMASCHINE,WVTEIL,WVMERkMAL)  w\n" +
                "LEFT JOIN MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                "LEFT JOIN  MASCHINE ma on ma.MAMASCHINE=w.WVMASCHINE\n" +
                "where   w.WVMASCHINE=?0 and w.WVTEIL=?1";
        if(StringUtils.isNotBlank(k0053)){
            sql += " and w.WVAUFTRAG="+k0053;
            cSql += " and w.WVAUFTRAG="+k0053;
        }
        return super.queryCustomBeanPageBySql(OverviewModel.class,cSql,sql,page-1,limit,k0010,k1000);
    }

    @Override
    public LusHeaderModel getLusHeader(String k0053, Integer k1000, Integer k0010,String startTime, String endTime) {
        HashMap<String,Object> map = new HashMap<>();
        String sql="SELECT top 1 \n" +
                "m.MANR k4092,\n" +
                "m.MABEZ k4093,\n" +
                "w.WVMASCHINE k0010,\n" +
                "t.TETEIL k1000,\n" +
                "t.TEBEZEICH k1002, \n" +
                "(SELECT MIN(WVDATZEIT) from WERTEVAR where w.WVMASCHINE=WVMASCHINE  and w.WVTEIL=WVTEIL ) beginTime,\n" +
                "(SELECT MAX(WVDATZEIT) from WERTEVAR where w.WVMASCHINE=WVMASCHINE  and w.WVTEIL=WVTEIL) endTime \n" +
                "from  WERTEVAR w \n" +
                "LEFT JOIN MASCHINE m on m.MAMASCHINE=w.WVMASCHINE\n" +
                "LEFT JOIN TEIL t on t.TETEIL=w.WVTEIL\n" +
                "where w.WVATTRIBUT!= 255 and  w.WVMASCHINE=:k0010  and t.TETEIL=:k1000 ";
                map.put("k0010",k0010);
                map.put("k1000",k1000);
                if(k0053 != null && StringUtils.isNotBlank(k0053)){
                    sql +=" and w.WVAUFTRAG=:k0053";
                    map.put("k0053",k0053);
                }
                if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
                    sql +=" and w.WVDATZEIT >=:startTime";
                    sql +=" and w.WVDATZEIT <=:endTime";
                    map.put("startTime",startTime);
                    map.put("endTime",endTime);
                }
        return super.queryCustomBeanBySql(LusHeaderModel.class,sql,map);
    }

    public List<ParameterModel> getCharacteristicsInHeader(String k0053, Integer k1000, Integer k0010){
        String sql = "select  WVTEIL k1000,WVMERkMAL k2000 from WERTEVAR where w.WVATTRIBUT!= 255 and   WVTEIL=?0  and WVMASCHINE=?1     ";
        if(StringUtils.isNotBlank(k0053)){
            sql +=" and WVAUFTRAG="+k0053;
        }
        sql += "  group by  WVTEIL,WVMERkMAL ";
        return super.queryCustomBeanListBySql(ParameterModel.class,sql,k1000,k0010);
    }

    public List<BoxModel> getBoxCharacteristics(String k0053, Integer k1000,Integer k2000, Integer k0010,String startTime,String endTime){
        String sql = "SELECT w.WVTEIL as  k1000,w.WVMERkMAL as k2000," +
                "(select MEMERkBEZ from MERkMAL where w.WVTEIL=METEIL and w.WVMERkMAL=MEMERkMAL ) as k2002," +
                "(select MEOGW from MERkMAL where w.WVTEIL=METEIL and w.WVMERkMAL=MEMERkMAL ) as up," +
                "(select MEUGW from MERkMAL where w.WVTEIL=METEIL and w.WVMERkMAL=MEMERkMAL ) as down" +
                " from WERTEVAR  w \n" +
                "   LEFT JOIN MERkMAL  m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL" +
                "   where w.WVATTRIBUT!= 255 and  w.WVTEIL=?0  and w.WVMASCHINE=?1  ";
                if(StringUtils.isNotBlank(k0053)){
                    sql+=" and  w.WVAUFTRAG="+k0053;
                }
                if(k2000 != null){
                    sql+=" and w.WVMERkMAL="+k2000;
                }
                if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
                    sql+=" and  w.WVDATZEIT >='"+startTime+"'";
                    sql+=" and  w.WVDATZEIT <='"+endTime+"'";
                }
        sql+=" group by  w.WVTEIL,w.WVMERkMAL";
        return super.queryCustomBeanListBySql(BoxModel.class,sql,k1000,k0010);
    }

    public List<Double> getTestValue(String k0053,Integer k1000,Short k2000,Integer k0010,String startTime,String endTime){
        String sql = "SELECT w.WVWERT as WVWERT from WERTEVAR as w where w.WVATTRIBUT!= 255 and   w.WVTEIL=?0 and w.WVMERkMAL=?1 and w.WVMASCHINE=?2 ";
        if(StringUtils.isNotBlank(k0053)){
            sql +=" and w.WVAUFTRAG="+k0053;
        }

        if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
            sql+=" and  w.WVDATZEIT >='"+startTime+"'";
            sql+=" and  w.WVDATZEIT <='"+endTime+"'";
        }
        sql +=" order by w.WVWERT ASC";
        return super.queryDoubleListBySql(sql,k1000,k2000,k0010);
    }

    public List<ChartValueModel>  getOrderList(Integer k1000, Short k2000, Integer k0010,String k0053,String startTime,String endTime){
        String sql= "select top 125 "+
                "\tm.METEIL k1000,\n" +
                "\tt.TEBEZEICH k1002,\n" +
                "\tm.MEMERkMAL k2000,\n" +
                "\tm.MEMERkBEZ k2002,\n" +
                "  w.WV0014 k0014,\n" +
                " ISNULL(w.WV0005, 'ok') k0005, \n" +
                "(case when w.WV0005 is null or w.WV0005='OK' then 1 else 0 end) scrap, \n" +
                " w.WVDATZEIT as dateTime,\n" +
                "\tw.WVCHARGE k0006,\n" +
                "\tm.MEUNTERSART  K2009 ," +
                "\tm.MEEINHEITTEXT K2142,\n" +
                "\tw.WVWERT k0001,\n" +
                " (CASE\n" +
                "WHEN w.WVWERT >= m.MEUGW and w.WVWERT <= m.MEOGW THEN 1 \n" +
                " WHEN w.WVWERT < (m.MEUGSCHROTT) or w.WVWERT > (m.MEOGSCHROTT) THEN 2 \n" +
                "ELSE 3 END) state\n" +
                "from WERTEVAR  w\n" +
                "LEFT JOIN  MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                "LEFT JOIN  TEIL t on w.WVTEIL=t.TETEIL\n" +
                "where w.WVATTRIBUT!= 255 and  w.WVTEIL=?0 and w.WVMERkMAL=?1  and w.WVMASCHINE=?2 ";
                if(StringUtils.isNotBlank(k0053)){
                    sql+="and w.WVAUFTRAG="+k0053;
                }
                if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
                    sql+=" and w.WVDATZEIT >='" +startTime+"'";
                    sql+=" and w.WVDATZEIT <='"+endTime+"'";
                }
        return super.queryCustomBeanListBySql(ChartValueModel.class,sql,k1000,k2000,k0010);
    }

    @Override
    public FeatureModel getCharacteristics(Integer k1000, Short k2000) {
        String sql = "SELECT  \n" +
                "t.TETEIL partId,\n" +
                " t.TEBEZEICH  part,\n" +
                "(case when m.MENENNMAS is null and m.MEUGW is not null and m.MEOGW is not null then (m.MEUGW+m.MEOGW)/2 end) k2101,\n" +
                "m.METOLERANZ k2104,\n" +
                "m.MEUGSCHROTT K2114,\n" +
                "m.MEOGSCHROTT K2115,\n" +
                "m.MEUGW k2110,\n" +
                "m.MEOGW k2111\n" ;
                sql+=" from MERkMAL m,TEIL t  where m.METEIL=t.TETEIL and m.METEIL=?0 and m.MEMERkMAL=?1";
        return super.queryCustomBeanBySql(FeatureModel.class,sql,k1000,k2000);
    }


    public List<OrderModel> getOrderListNew(String filters, Integer machineId,Integer k1000 ,String startTime, String endTime){
        HashMap<String,Object> map = new HashMap<>();
        String sql = "SELECT a.*,\n" +
                "'"+startTime+"' as minTime, GETDATE() as maxTime,"+
                "(SELECT t.TETEILNR from  TEIL t where t.TETEIL=a.WVTEIL) as k1001,\n" +
                "(SELECT t.TEBEZEICH from  TEIL t where t.TETEIL=a.WVTEIL) as k1002\n" +
                "from (\n" +
                "SELECT WVTEIL,count(1) [count] from WERTEVAR  where WVATTRIBUT!= 255 and  WVDATZEIT>=:startTime and WVDATZEIT<=:endTime and WVMASCHINE = :machineId and  WVTEIL =:k1000  group by  WVTEIL\n" +
                ") a";
        map.put("startTime",startTime);
        map.put("endTime",endTime);
        map.put("machineId",machineId);
        map.put("k1000",k1000);
        return super.queryCustomBeanListBySql(OrderModel.class,sql,map);
    }

    public List<OverviewModel> getCharacteristicsByOrderId(Short filter, Integer machineId, String startTime, String endTime,String k0053,Integer k1000){
        HashMap<String,Object> map = new HashMap<>();

        String sql = "select "+
                "\tm.METEIL k1000,\n" +
                "\tm.MEMERkMAL k2000,\n" +
                "\tm.MEMERkBEZ k2002,\n" +
                "\t(case when m.MENENNMAS is null and m.MEUGW is not null and m.MEOGW is not null then (m.MEUGW+m.MEOGW)/2 end) k2101,\n" +
                "\tm.MEUGW k2110,\n" +
                "\tm.MEOGW k2111,\n" +
                "cp.cp as cpTarget,\n" +
                "cp.cpk as cpkTarget\n" +
                " from  MERkMAL m " +
                " left join  (select   WVTEIL,WVMERKMAL,WVMASCHINE,WVAUFTRAG  from WERTEVAR where WVATTRIBUT!= 255 and  WVDATZEIT>=:startTime and WVDATZEIT <=:endTime group by WVTEIL,WVMERKMAL,WVMASCHINE,WVAUFTRAG )  w on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL  " +
                " left join cp_config cp on cp.importance_rank=m.MEMERKKLASSE\n" +
                " where  m.METEIL=:k1000 and m.MEUGW is not null and m.MEOGW  is not null and w.WVMASCHINE is not null";
        map.put("startTime",startTime);
        map.put("endTime",endTime);
        map.put("k1000",k1000);
        if(StringUtils.isNotBlank(k0053)){
            sql +=" and  w.WVAUFTRAG=:k0053";
            map.put("k0053",k0053);
        }

        if(filter != null){
            sql +=" and  m.MEMERkkLASSE =:filter";
            map.put("filter",filter);
        }
        if(machineId != null){
            sql+=" and w.WVMASCHINE = :machineId ";
            map.put("machineId",machineId);
        }
        return super.queryCustomBeanListBySql(OverviewModel.class,sql,map);

    }

    public List<Double> getValueByOrderId(Integer machineId,Integer k1000,Short k2000, String startTime, String endTime,String k0053){
        HashMap<String,Object> map = new HashMap<>();
        String sql = "select top 125 w.WVWERT "+
                " from WERTEVAR w  " +
                " left join   MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL  " +
                "  where w.WVATTRIBUT!= 255 and  w.WVDATZEIT > :startTime  and w.WVDATZEIT <=:endTime and w.WVTEIL=:k1000 and w.WVMERkMAL=:k2000";
        map.put("startTime",startTime);
        map.put("endTime",endTime);
        map.put("k1000",k1000);
        map.put("k2000",k2000);
        if(StringUtils.isNotBlank(k0053)){
            sql +="and  w.WVAUFTRAG=:k0053";
            map.put("k0053",k0053);
        }
        if(machineId != null ){
            sql+=" and w.WVMASCHINE =:machineId ";
            map.put("machineId",machineId);
        }
        return super.queryDoubleListBySql(sql,map);

    }

    public List<GroupModel> getMachineGroupModer(String filters, Set<Integer> machineIds, String startTime, String endTime){
        HashMap<String,Object> map = new HashMap<>();

        String sql = "SELECT " +
                " w.WVMASCHINE  as id, " +
                " (select MABEZ from MASCHINE where MAMASCHINE=w.WVMASCHINE)  as name, " +
                " MAX(w.WVDATZEIT) as maxTime," +
                " MIN(w.WVDATZEIT) as minTime," +
                " COUNT(1) as [count]," +
                " count(DISTINCT w.WVTEIL)  numberOfParts" +
                " from WERTEVAR w left join MERkMAL m on m.METEIL = w.WVTEIL and m.MEMERkMAL=WVMERkMAL \n" +
                " where w.WVATTRIBUT!= 255 and w.WVDATZEIT >=:startTime  and w.WVDATZEIT <=:endTime ";
        map.put("startTime",startTime);
        map.put("endTime",endTime);
        if(filters != null && !filters.equals("")){
            String[] arrays = filters.split(",");
            sql +=" and  m.MEMERkkLASSE in (:filters)";
            map.put("filters",Arrays.asList(arrays));
        }
        if(machineIds != null && machineIds.size() > 0 ){
            sql+=" and w.WVMASCHINE in (:machineIds) ";
            map.put("machineIds",Arrays.asList(machineIds.toArray()));
        }
        sql+=" GROUP BY w.WVMASCHINE ";
        return super.queryCustomBeanListBySql(GroupModel.class,sql,map);
    }

    public List<GroupModel> getMachineGroupModer(String filters, Set<Integer> machineIds,Set<Integer> parts,Set<Integer> nests, String startTime, String endTime){
        HashMap<String,Object> map = new HashMap<>();

        String sql = "SELECT " +
                " w.WVMASCHINE  as id, " +
                " (select MABEZ from MASCHINE where MAMASCHINE=w.WVMASCHINE)  as name, " +
                " MAX(w.WVDATZEIT) as maxTime," +
                " MIN(w.WVDATZEIT) as minTime," +
                " COUNT(1) as [count]," +
                " count(DISTINCT w.WVTEIL)  numberOfParts" +
                " from WERTEVAR w left join MERkMAL m on m.METEIL = w.WVTEIL and m.MEMERkMAL=WVMERkMAL \n" +
                " where w.WVATTRIBUT!= 255 and  w.WVDATZEIT >=:startTime  and w.WVDATZEIT <=:endTime ";
        map.put("startTime",startTime);
        map.put("endTime",endTime);
        if(filters != null && !filters.equals("")){
            String[] arrays = filters.split(",");
            sql +=" and  m.MEMERkkLASSE in (:filters)";
            map.put("filters",Arrays.asList(arrays));
        }
        if(machineIds != null && machineIds.size() > 0 ){
            sql+=" and w.WVMASCHINE in (:machineIds) ";
            map.put("machineIds",Arrays.asList(machineIds.toArray()));
        }
        if(parts != null && parts.size() > 0 ){
            sql+=" and w.WVTEIL in (:partId) ";
            map.put("partId",Arrays.asList(parts.toArray()));
        }

        if(nests != null ){
            sql+=" and w.WVNEST in (:nestIds) ";
            map.put("nestIds",Arrays.asList(nests.toArray()));
        }

        sql+=" GROUP BY w.WVMASCHINE ";
        return super.queryCustomBeanListBySql(GroupModel.class,sql,map);
    }

    public List<GroupModel> getMachineGroupModerList(String filters,Set<Integer> k0010, String startTime, String endTime){
        HashMap<String,Object> map = new HashMap<>();

        String sql = "SELECT " +
                " w.WVMASCHINE  as id, " +
                " (select MABEZ from MASCHINE where MAMASCHINE=w.WVMASCHINE)  as name, " +
                " MAX(w.WVDATZEIT) as maxTime," +
                " MIN(w.WVDATZEIT) as minTime," +
                " COUNT(1) as [count]," +
                " count(DISTINCT w.WVTEIL)  numberOfParts" +
                " from WERTEVAR w left join MERkMAL m on m.METEIL = w.WVTEIL and m.MEMERkMAL=WVMERkMAL \n" +
                " where w.WVATTRIBUT!= 255 and  w.WVDATZEIT >=:startTime  and w.WVDATZEIT <=:endTime ";
        map.put("startTime",startTime);
        map.put("endTime",endTime);
        if(filters != null && !filters.equals("")){
            String[] arrays = filters.split(",");
            sql +=" and  m.MEMERkkLASSE in (:filters)";
            map.put("filters",Arrays.asList(arrays));
        }

        if(k0010 != null ){
            sql+=" and w.WVMASCHINE in (:k0010) ";
            map.put("k0010",Arrays.asList(k0010.toArray(new Integer[0])));
        }
        sql+=" GROUP BY w.WVMASCHINE ";
        return super.queryCustomBeanListBySql(GroupModel.class,sql,map);
    }

    public List<GroupModel> getPartGroupModerList(String filters , Integer machineId, String startTime, String endTime){
        HashMap<String,Object> map = new HashMap<>();

        String sql = "SELECT " +
                " w.WVTEIL as id," +
                " (SELECT t.TEBEZEICH from TEIL t where w.WVTEIL=t.TETEIL ) as name ," +
                " w.WVAUFTRAG as orderId,"+
                " MAX(w.WVDATZEIT) as maxTime," +
                " MIN(w.WVDATZEIT) as minTime," +
                " COUNT(1) as [count]," +
                " count(DISTINCT w.WVTEIL)  numberOfParts" +
                " from WERTEVAR w left join MERkMAL m on m.METEIL = w.WVTEIL and m.MEMERkMAL=WVMERkMAL \n" +
                " where w.WVATTRIBUT!= 255 and  w.WVDATZEIT >=:startTime  and w.WVDATZEIT <=:endTime ";
        map.put("startTime",startTime);
        map.put("endTime",endTime);
        if(filters != null && !filters.equals("")){
            String[] arrays = filters.split(",");
            sql +=" and  m.MEMERkkLASSE in (:filters)";
            map.put("filters",Arrays.asList(arrays));
        }
        if(machineId != null ){
            sql+=" and w.WVMASCHINE =:machineId ";
            map.put("machineId",machineId);
        }
        sql+=" GROUP BY w.WVTEIL,w.WVAUFTRAG ";
        return super.queryCustomBeanListBySql(GroupModel.class,sql,map);
    }

    public List<GroupModel> getPartGroupModerList(String filters, Set<Integer> machineIds,Set<Integer> nestIds, String startTime, String endTime){
        HashMap<String,Object> map = new HashMap<>();

        String sql = "SELECT " +
                " w.WVTEIL as id," +
                " (SELECT t.TEBEZEICH from TEIL t where w.WVTEIL=t.TETEIL ) as name ," +
                " MAX(w.WVDATZEIT) as maxTime," +
                " MIN(w.WVDATZEIT) as minTime," +
                " COUNT(1) as [count]," +
                " count(DISTINCT w.WVTEIL)  numberOfParts" +
                " from WERTEVAR w left join MERkMAL m on m.METEIL = w.WVTEIL and m.MEMERkMAL=WVMERkMAL \n" +
                " where w.WVATTRIBUT!= 255 and  w.WVDATZEIT >=:startTime  and w.WVDATZEIT <=:endTime ";
        map.put("startTime",startTime);
        map.put("endTime",endTime);
        if(filters != null && !filters.equals("")){
            String[] arrays = filters.split(",");
            sql +=" and  m.MEMERkkLASSE in (:filters)";
            map.put("filters",Arrays.asList(arrays));
        }
        if(machineIds != null ){
            sql+=" and w.WVMASCHINE in (:machineIds) ";
            map.put("machineIds",machineIds);
        }
        if(nestIds != null ){
            sql+=" and w.WVNEST in (:nestIds) ";
            map.put("nestIds",nestIds);
        }
        sql+=" GROUP BY w.WVTEIL ";
        return super.queryCustomBeanListBySql(GroupModel.class,sql,map);
    }


    public List<OverviewModel> getCharacteristicsByProcessAndMachine(String filters,  Set<Integer> machineIds, Integer partId,String startTime, String endTime){
        HashMap<String,Object> map = new HashMap<>();

        String sql = "select "+
                "\tm.METEIL k1000,\n" +
                "\tm.MEMERkMAL k2000,\n" +
                "\tm.MEMERkBEZ k2002,\n" +
                "\t(case when m.MENENNMAS is null and m.MEUGW is not null and m.MEOGW is not null then (m.MEUGW+m.MEOGW)/2 end) k2101,\n" +
                "\tm.MEUGW k2110,\n" +
                "\tm.MEOGW k2111,\n" +
                "cp.cp as cpTarget,\n" +
                "cp.cpk as cpkTarget\n" +
                " from WERTEVAR w  " +
                " left join   MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL  " +
                " left join cp_config cp on cp.importance_rank=m.MEMERKKLASSE\n" +
                " where w.WVATTRIBUT!= 255 and  w.WVDATZEIT >=:startTime  and w.WVDATZEIT <=:endTime ";
        map.put("startTime",startTime);
        map.put("endTime",endTime);
        if(filters != null && !filters.equals("")){
            String[] arrays = filters.split(",");
            sql +=" and  m.MEMERkkLASSE in (:filters)";
            map.put("filters",Arrays.asList(arrays));
        }
        if(machineIds != null && machineIds.size() > 0 ){
            sql+=" and w.WVMASCHINE in (:machineIds) ";
            map.put("machineIds",Arrays.asList(machineIds.toArray()));
        }

        if(partId != null){
            sql+=" and w.WVTEIL in(:partId) ";
            map.put("partId",partId);
        }
        return super.queryCustomBeanListBySql(OverviewModel.class,sql,map);

    }

    public List<OverviewModel> getCharacteristicsByProcessAndMachine(String filters, Set<Integer> machineIds, Set<Integer> partId,String nestIds,String startTime, String endTime){
        HashMap<String,Object> map = new HashMap<>();

        String sql = "select "+
                "\tm.METEIL k1000,\n" +
                "\tm.MEMERkMAL k2000,\n" +
                "\tm.MEMERkBEZ k2002,\n" +
                "\t(case when m.MENENNMAS is null and m.MEUGW is not null and m.MEOGW is not null then (m.MEUGW+m.MEOGW)/2 end) k2101,\n" +
                "\tm.MEUGW k2110,\n" +
                "\tm.MEOGW k2111,\n" +
                " cp.cp as cpTarget,\n" +
                " cp.cpk as cpkTarget\n" +
                "\t  from  \n" +
                " (select w.WVTEIL,w.WVMERkMAL from  WERTEVAR w  "+
                " where w.WVATTRIBUT!= 255 and  w.WVDATZEIT >=:startTime  and w.WVDATZEIT <=:endTime ";
        map.put("startTime",startTime);
        map.put("endTime",endTime);
        if(filters != null && !filters.equals("")){
            String[] arrays = filters.split(",");
            sql +=" and  m.MEMERkkLASSE in (:filters)";
            map.put("filters",Arrays.asList(arrays));
        }
        if(machineIds != null && machineIds.size() > 0 ){
            sql+=" and w.WVMASCHINE in (:machineIds) ";
            map.put("machineIds",Arrays.asList(machineIds.toArray()));
        }

        if(partId != null && partId.size() > 0 ){
            sql+=" and w.WVTEIL in (:partId) ";
            map.put("partId",Arrays.asList(partId.toArray()));
        }

        if(StringUtils.isNotBlank(nestIds) ){
            String[] array = nestIds.split(",");
            sql+=" and w.WVNEST in (:nestIds) ";
            map.put("nestIds",Arrays.asList(array));
        }

        sql +=" group by w.WVTEIL,w.WVMERkMAL ) a  left join   MERkMAL m on a.WVTEIL=m.METEIL and a.WVMERkMAL=m.MEMERkMAL" +
                " left join cp_config cp on cp.importance_rank=m.MEMERKKLASSE\n";
        return super.queryCustomBeanListBySql(OverviewModel.class,sql,map);

    }


    public List<Double> getValueByProcessAndMachine(String filters, Set<Integer> machineIds,Integer partId, String startTime, String endTime){
        HashMap<String,Object> map = new HashMap<>();
        String sql = "select w.WVWERT "+
                " from WERTEVAR w  " +
                " left join   MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL  " +
                "  where w.WVATTRIBUT!= 255 and  w.WVDATZEIT >=:startTime  and w.WVDATZEIT <=:endTime  ";
        map.put("startTime",startTime);
        map.put("endTime",endTime);
        if(filters != null && !filters.equals("")){
            String[] arrays = filters.split(",");
            sql +=" and  m.MEMERkkLASSE in (:filters)";
            map.put("filters",Arrays.asList(arrays));
        }
        if(machineIds != null && machineIds.size() > 0 ){
            sql+=" and w.WVMASCHINE in (:machineIds) ";
            map.put("machineIds",Arrays.asList(machineIds.toArray()));
        }
        if(partId != null){
            sql+=" and w.WVTEIL =:partId ";
            map.put("partId",partId);
        }
        return super.queryDoubleListBySql(sql,map);

    }

    public List<Double> getValueByProcessAndMachine(String filters, Set<Integer> machineIds,Set<Integer> partIds,String nestIds ,String startTime, String endTime){
        HashMap<String,Object> map = new HashMap<>();
        String sql = "select w.WVWERT "+
                " from WERTEVAR w  " +
                " left join   MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL  " +
                "  where w.WVATTRIBUT!= 255 and  w.WVDATZEIT >=:startTime  and w.WVDATZEIT <=:endTime  ";
        map.put("startTime",startTime);
        map.put("endTime",endTime);
        if(filters != null && !filters.equals("")){
            String[] arrays = filters.split(",");
            sql +=" and  m.MEMERkkLASSE in (:filters)";
            map.put("filters",Arrays.asList(arrays));
        }
        if(machineIds != null && machineIds.size() > 0 ){
            sql+=" and w.WVMASCHINE in (:machineIds) ";
            map.put("machineIds",Arrays.asList(machineIds.toArray()));
        }
        if(partIds != null){
            sql+=" and w.WVTEIL in(:partIds) ";
            map.put("partIds",partIds);
        }
        if(StringUtils.isNotBlank(nestIds) ){
            String[] array = nestIds.split(",");
            sql+=" and w.WVNEST in (:nestIds) ";
            map.put("nestIds",Arrays.asList(array));
        }

        return super.queryDoubleListBySql(sql,map);

    }



    @Override
    public List<Double> getAllValueByUser(Set<Integer> machineIds, String filters, String startTime, String endTime) {
        HashMap<String,Object> map = new HashMap<>();
        String sql = "select w.WVWERT "+
                " from WERTEVAR w  " +
                " left join   MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL  " +
                "  where w.WVATTRIBUT!= 255 and  w.WVDATZEIT >=:startTime  and w.WVDATZEIT <=:endTime  ";
        map.put("startTime",startTime);
        map.put("endTime",endTime);
        if(filters != null && !filters.equals("")){
            String[] arrays = filters.split(",");
            sql +=" and  m.MEMERkkLASSE in (:filters)";
            map.put("filters",Arrays.asList(arrays));
        }

        if(machineIds != null && machineIds.size() > 0 ){
            sql+=" and w.WVMASCHINE in (:machineIds) ";
            map.put("machineIds",Arrays.asList(machineIds.toArray()));
        }
        return super.queryDoubleListBySql(sql,map);
    }


    public List<GroupModel> getAnalyseNest(String filterId,Set<Integer> machineIds,String partIds,String nestIds,String startTime, String endTime ){
        HashMap<String,Object> map = new HashMap<>();

        String sql = "SELECT " +
                " w.WVNEST as id," +
                " cast(w.WVNEST as varchar(64)) as name ," +
                " MAX(w.WVDATZEIT) as maxTime," +
                " MIN(w.WVDATZEIT) as minTime," +
                " COUNT(1) as [count]," +
                " count(DISTINCT w.WVTEIL)  numberOfParts" +
                " from WERTEVAR w left join MERkMAL m on m.METEIL = w.WVTEIL and m.MEMERkMAL=w.WVMERkMAL \n" +
                " where w.WVATTRIBUT!= 255 and  w.WVDATZEIT >=:startTime  and w.WVDATZEIT <=:endTime ";
        map.put("startTime",startTime);
        map.put("endTime",endTime);
        if(filterId != null && !filterId.equals("")){
            String[] arrays = filterId.split(",");
            sql +=" and  m.MEMERkkLASSE in (:filters)";
            map.put("filterId",Arrays.asList(arrays));
        }
        if(machineIds != null && machineIds.size() >0 ){
            sql+=" and w.WVMASCHINE in (:machineIds) ";
            map.put("machineIds",machineIds);
        }

        if(StringUtils.isNotBlank(partIds)){
            String[] arrays = partIds.split(",");
            sql+=" and w.WVTEIL in (:partIds) ";
            map.put("partIds",Arrays.asList(arrays));
        }

        if(StringUtils.isNotBlank(nestIds) ){
            String[] array = nestIds.split(",");
            sql+=" and w.WVNEST in (:nestIds) ";
            map.put("nestIds",Arrays.asList(array));
        }
        sql +=" group by w.WVNEST ";
        return super.queryCustomBeanListBySql(GroupModel.class,sql,map);
    }

    @Override
    public ResultPage<EventMeasuresModel> getEventMeasures(Integer page, Integer limit) {
        String sql = "SELECT w.WV0005 as eventName,w.WVDATZEIT as  eventTime,t.TEBEZEICH as partName,t.TETYP as partType  from WERTEVAR w LEFT JOIN  TEIL t on w.WVTEIL=t.TETEIL where w.WVATTRIBUT!= 255";
        String csql = "SELECT count(1)  from WERTEVAR w LEFT JOIN  TEIL t on w.WVTEIL=t.TETEIL where w.WVATTRIBUT!= 255 ";
        return super.queryCustomBeanPageBySql(EventMeasuresModel.class,sql,csql,page,limit);
    }

    @Override
    public List<ProductionStationModel> productionStation() {
        String sql = "SELECT cast((case when a.total != 0 then (a.qualified/cast(a.total as decimal(5,2))) else 0 end) as decimal(5,2)) as percentOfPass,a.id,a.name from (\n" +
                "SELECT \n" +
                "(SELECT count(1) from WERTEVAR w   where w.WVPRUEFER = p.PRPRUEFER and DATEDIFF(day,w.WVDATZEIT,GETDATE())=0\t) total,\n" +
                "(SELECT count(1) from WERTEVAR w LEFT JOIN MERKMAL m on m.METEIL = w.WVTEIL and m.MEMERkMAL=w.WVMERkMAL where w.WVPRUEFER = p.PRPRUEFER and DATEDIFF(day,w.WVDATZEIT,GETDATE())=0\tand w.WVWERT>= m.MEUGW and w.WVWERT<=m.MEOGW ) as qualified,\n" +
                "p.PRPRUEFER as id,p.PRNAME+p.PRVORNAME as name\n" +
                "from PRUEFER p\n" +
                ") a ";
        return super.queryCustomBeanListBySql(ProductionStationModel.class,sql);
    };


    public List<OverviewModel> getPartCharacteristics(String k0014){
        String sql = "select "+
                "\tm.METEIL k1000,\n" +
                "\tm.MEMERkMAL k2000,\n" +
                "\tm.MEMERkBEZ k2002,\n" +
                "\t(case when m.MENENNMAS is null and m.MEUGW is not null and m.MEOGW is not null then (m.MEUGW+m.MEOGW)/2 end) k2101,\n" +
                "\tm.MEUGW k2110,\n" +
                "\tm.MEOGW k2111,\n" +
                "\tcp.cp as cpTarget,\n" +
                "\tcp.cpk as cpkTarget\n" +
                "\tfrom   MERkMAL m  \n" +
                "\tleft join cp_config cp on cp.importance_rank=m.MEMERKKLASSE\n"+
                "\tleft join WERTEVAR w on m.METEIL = w.WVTEIL and m.MEMERkMAL=w.WVMERkMAL  WHERE w.WVATTRIBUT!= 255 and   w.WV0014=?0 \n";
        return super.queryCustomBeanListBySql(OverviewModel.class,sql,k0014);
    }

    public ResultPage<CharacteristicsModel> getCharacteristicsByK0014(String k0014,Integer k0010,Integer page,Integer limit){
        String sql = " select \n" +
                "\tm.METEIL k1000,\n" +
                "\tm.MEMERkMAL k2000,\n" +
                "\tm.MEMERkBEZ k2002,\n" +
                "\t(case when m.MENENNMAS is null and m.MEUGW is not null and m.MEOGW is not null then (m.MEUGW+m.MEOGW)/2 end) k2101,\n" +
                "\tm.MEUGW k2110,\n" +
                "\tm.MEOGW k2111,\n" +
                "\tm.MEUGSCHROTT K2114,\n" +
                "\tm.MEOGSCHROTT K2115,\n" +
                "  w.WV0056 code,\n" +
                "\tw.WVDATZEIT dateTime,\n" +
                "\tw.WVWERT k0001,\n" +
                "\tm.MEEINHEITTEXT k2142,\n" +
                "\tm.MEUNTERSART k2009" +
                " from   WERTEVAR  w\n" +
                " LEFT JOIN  MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                " LEFT JOIN  TEIL t on w.WVTEIL=t.TETEIL\n" +
                " where  w.WVATTRIBUT != 255 and  w.WV0014=?0  and w.WVMASCHINE=?1 ";
        String cSql = "SELECT\n " +
                " count(1) " +
                " from  WERTEVAR w\n" +
                " LEFT JOIN  MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                " LEFT JOIN  TEIL t on w.WVTEIL=t.TETEIL\n" +
                " where   w.WVATTRIBUT != 255 and w.WV0014=?0  and w.WVMASCHINE=?1 ";
        return super.queryCustomBeanPageBySql(CharacteristicsModel.class,cSql,sql,page-1,limit,k0014,k0010);
    }

    public List<CharacteristicsModel> getCharacteristicsByK0014AndK0010(String k0014,Integer k0010){
        String sql = " select \n" +
                "\tm.METEIL k1000,\n" +
                "\tm.MEMERkMAL k2000,\n" +
                "\tm.MEMERkBEZ k2002,\n" +
                "\t(case when m.MENENNMAS is null and m.MEUGW is not null and m.MEOGW is not null then (m.MEUGW+m.MEOGW)/2 end) k2101,\n" +
                "\tm.MEUGW k2110,\n" +
                "\tm.MEOGW k2111,\n" +
                "\tm.MEUGSCHROTT K2114,\n" +
                "\tm.MEOGSCHROTT K2115,\n" +
                "w.WV0056 code,\n" +
                "\tw.WVDATZEIT dateTime,\n" +
                "\tw.WVWERT k0001,\n" +
                "\tm.MEEINHEITTEXT k2142,\n" +
                "\tm.MEUNTERSART k2009" +
                " from WERTEVAR  w\n" +
                " LEFT JOIN  MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                " LEFT JOIN  TEIL t on w.WVTEIL=t.TETEIL\n" +
                " where w.WV0014=?0  and w.WVMASCHINE=?1 and w.WVATTRIBUT != 255";
        return super.queryCustomBeanListBySql(CharacteristicsModel.class,sql,k0014,k0010);
    }

    public Integer hasCurvesFile(String k0014,Integer k0010){
        String sql ="select count(WV0056) from WERTEVAR where WVATTRIBUT!= 255 and  WV0014=?0  and WVMASCHINE=?1 and WV0056 is not null and WV0056 !='' ";
        return super.queryIntegerBySql(sql,k0014,k0010);
    }

    public List<CharacteristicsModel> querymERKMAL(){
        String sql ="select DISTINCT WVTEIL as k1000,WVMERKMAL as k2000 from WERTEVAR  where WVATTRIBUT!= 255  and DATEDIFF(hh, CREATETIME, GETDATE())<=2 ";
        //and DATEDIFF( DAY, WVDATZEIT, GETDATE( ) ) = 0
        return super.queryCustomBeanListBySql(CharacteristicsModel.class,sql);
    }

    public List<Double> queryLastValueByK1000AndK2000(Integer k1000, Short k2000){
        String sql = "SELECT top 125 w.WVWERT as WVWERT from WERTEVAR as w where w.WVATTRIBUT!= 255  and   w.WVTEIL=?0 and w.WVMERkMAL=?1 ";
        sql +=" order by w.WVWERTNR desc";
        return super.queryDoubleListBySql(sql,k1000,k2000);
    }


    public CloudMessageModel queryCloudHomeMessage(Integer k1000, Short k2000, Integer k0000){
        String sql = "SELECT \n" +
                "ma.MABEZ as k0010,\n" +
                "m.MEMERKBEZ as k2002,\n" +
                "m.MEUGW as k2111,\n" +
                "m.MEOGW as k2110,\n" +
                "ISNULL(w.WVWERT,0.00) as k0001,\n" +
                "w.WVDATZEIT as k0004,\n" +
                "w.WV0014 as k0014"+
                "from  WERTEVAR  w\n" +
                "LEFT JOIN  MERkMAL m on w.WVTEIL=m.METEIL and w.WVMERkMAL=m.MEMERkMAL\n" +
                "LEFT JOIN  TEIL t on w.WVTEIL=t.TETEIL\n" +
                "LEFT JOIN MASCHINE ma on ma.MAMASCHINE=w.WVMASCHINE\n" +
                "where w.WVTEIL =:k1000 and w.WVMERKMAL=:k2000  and w.WVWERTNR =:k0000";
        return super.queryCustomBeanBySql(CloudMessageModel.class,sql,k1000,k2000,k0000);
    }


    public CharacteristicsModel queryCharacteristicsByK1000AndK2000(Integer k1000,Short k2000){
        String sql ="SELECT t.TETEIL k1000,t.TEBEZEICH k1002,m.MEMERKMAL k2000,m.MEMERKBEZ k2002,m.MEEINHEITTEXT k2142,m.MEOGW K2111,m.MEUGW K2110,(case when m.MENENNMAS is null and m.MEUGW is not null and m.MEOGW is not null then (m.MEUGW+m.MEOGW)/2 end) k2101 \n" +
                "from MERKMAL m  \n" +
                "LEFT JOIN TEIL t on t.TETEIL=m.METEIL\n" +
                "where m.METEIL =?0  and m.MEMERKMAL=?1 ";
        return super.queryCustomBeanBySql(CharacteristicsModel.class,sql,k1000,k2000);
    }
}
